在之前的文章「技術棧 (technical stack) 的決策」，有討論過 SQL 2003 之後又有許多新增的 SQL 語法。要一一研究完那些語法，相當地花時間，然而，可能也沒有需要，因為通常一間公司、一個部門會用到的都只有部分的子集合而已。當然，如果你的工作也是 IT 顧問，那就另當別論。

這邊列舉四個算是資料處理、資料分析領域常見的題目，讓讀者來一覽 SQL 2003 的奧妙吧：

1. 每個組別的前 n 位
2. 樞紐分析
3. 週期比較
4. 時序資料的統計結果

每個組別的前 n 位 (top n per group)

若 cites 這張表如下，想要用 SQL query 來找出「在每個國家，人口最多的三個城市？」

我們可以用如下的 Query 來達成：

SELECT
  *
FROM
  (
    SELECT
      country,
      city,
      population,
      row_number() OVER (
        PARTITION BY country
        ORDER BY
          population desc
      ) AS country_rank
    FROM
      cities
  ) ranks
WHERE
  country_rank <= 3;

此處最關鍵的技巧是使用 SQL window function 的 row_number() 函數。

樞紐分析 (pivot table)

在我最初研究這個樞紐分析時，曾經一度以為，樞紐分析就只是 SQL 的 group by 的應用而已，所以我自以為寫下了如下的 SQL 就可以做出「樞紐分析」。

SELECT
    c1,
    c2,
    aggregate(c3)
FROM
    table_name
GROUP BY
    c1, c2;

但是，我太小看樞紐分析了，試算表裡的樞紐分析總是有著一列叫做「小計」啊！！

而實際上，SQL 早也提供了解決方案。改成如下，就可以做出樞紐分析了！

SELECT
    c1,
    c2,
    aggregate(c3)
FROM
    table_name
GROUP BY
    ROLLUP(c1, c2);

註：如果沒有 ROLLUP 的話，其實樞紐分析還是可以透過幾個 SQL query 做 union 而做出來。

週期比較 (comparing time periods)

在業務單位的報表，很常需要做下列的幾種週期比較：

• 不同年度之間月業績的比較 (month over month)
• 不同年度之間季度業績的比較 (quarter over quarter)
• 不同年度之間年業績的比較 (year over year)

這類的報表，常常會需要計算「2022年3月的業績」減「2021年3月的業績」。
這邊有兩種作法：

1. 如果是在 tranformation layer 來完成的話，建議使用 SQL window function 的 lag() 函數。
2. 如果是在 Metabase 裡來設法做出的話，建議使用 self-join 來實現。(Metabase 的 Query builder 還不支援 window function。)

時序資料的統計結果 (statistical time series results)

假設有一些照時間分布的原始資料 (time series data)，想要去對時間做分群，做出每個月的統計結果 (statistical time series results)。如果每個月都有資料的話，一個簡單的分群與匯總 (group by + aggregate) 就可以結案了。偏偏，不幸的事發生了：
「有某幾個月分，恰好沒有資料」。

那該怎麼處理呢？

解決方案如下：

1. 先用一組特別的 SQL query ，通常稱之為 date spine ，它會對應到「要統計的區間裡所有的時間資料」。
2. 把原始資料與 date spine 做 left join 之後，再做分群與匯總 (group by + aggregate)

註：在 DuckDB/Postgres 都有提供 generate_series 函數，可以用來生成 date spine 。除此之外，dbt-utils 的 package 也有 date_spine 的函數。

後記

有一回，我示範了幾招 SQL 進階技巧給客戶看，客戶震驚之餘，忍不住問了我：「如果我一直沒有機會學到這個，那我該怎麼用普通的 SQL 來解這些問題？」

「嗯，你可以考慮 join 到天荒地老(註1)、或是 union 到海枯石爛(註2)，也是有辦法扺達終點的。」

註：

1. 沒有 window function 的話，『週期比較』還是可以透過 self-join 來達成。
2. 沒有 rollup 函數的話，『樞紐分析』還是可以透過 union 來做出。

其它資源

1. 對 dbt 或 data 有興趣 👋？歡迎加入 dbt community 到 #local-taipei 找我們，也有實體 Meetup 請到 dbt Taipei Meetup 報名參加
2. 歡迎訂閱 PruningSuccess 電子報，主要談論軟體開發、資料處理、資料分析等議題。